using Dapper;
using log4net;
using System;
using System.Collections.Generic;
using System.Data;

/**
*┌──────────────────────────────────────────────────────────────┐
*│　描    述：采购主表接口实现
*│　作    者：李宝
*│　版    本：1.0 模板代码自动生成
*│　创建时间：2019-01-05 16:55:24
*└──────────────────────────────────────────────────────────────┘
*┌──────────────────────────────────────────────────────────────┐
*│　命名空间： Usido.CRM.Repository
*│　类    名： SupplyOrderRepository
*└──────────────────────────────────────────────────────────────┘
*/

using Usido.CRM.Core.DbHelper;
using Usido.CRM.Core.Extensions;
using Usido.CRM.Core.Options;
using Usido.CRM.Core.Repository;
using Usido.CRM.Core.ToolHelper;
using Usido.CRM.Dto;
using Usido.CRM.IRepository;
using Usido.CRM.Models;

namespace Usido.CRM.Repository
{
    public class SupplyOrderRepository : BaseRepository<SupplyOrder, int>, ISupplyOrderRepository
    {
        private readonly ILog logger = LogManager.GetLogger(nameof(SupplyOrderRepository));

        public SupplyOrderRepository()
        {
            _dbConnection = ConnectionFactory.CreateConnection(DbOptionData.DbType, DbOptionData.ConnectionString);
        }

        public int AddSupply(SupplyOrder order, List<SupplyInfo> orderInfoList)
        {
            IDbTransaction transaction;
            transaction = _dbConnection.BeginTransaction();

            string sqlAddSupply = @"
INSERT INTO dbo.SupplyOrder(OrderCode,SupplierId,CreateTime,CreateUserId,State,Remark)
VALUES(@OrderCode,@SupplierId,GETDATE(),@CreateUserId,0,@Remark)
SELECT @@IDENTITY";

            string sqlAddSupplyInfo = @"
INSERT INTO dbo.SupplyInfo(ProductId,BatchNumber,Price,Quantity,DateExpiry,OrderId)
VALUES(@ProductId,@BatchNumber,@Price,@Quantity,@DateExpiry,@OrderId)
";
            try
            {
                order.OrderCode = Tools.GetOrderCode(3);
                int orderId = _dbConnection.ExecuteScalar<int>(sqlAddSupply, order, transaction);
                if (orderId <= 0)
                {
                    transaction.Rollback();
                    return -1;
                }

                foreach (var item in orderInfoList)
                {
                    item.OrderId = orderId;
                    _dbConnection.Execute(sqlAddSupplyInfo, item, transaction);
                }

                transaction.Commit();

                return orderId;
            }
            catch (Exception ex)
            {
                logger.Error("添加采购信息时出错", ex);
                transaction.Rollback();

                return -1;
            }
        }

        public bool UpdateSupply(SupplyOrder order, List<SupplyInfo> orderInfoList)
        {
            IDbTransaction transaction;
            transaction = _dbConnection.BeginTransaction();

            string sqlUpdateSupply = @"UPDATE dbo.SupplyOrder SET SupplierId = @SupplierId,Remark = @Remark WHERE Id = @Id";
            string sqlDeleteSupplyInfo = @"DELETE dbo.SupplyInfo WHERE OrderId = @OrderId";
            string sqlUpdateSupplyInfo = @"
INSERT INTO dbo.SupplyInfo(ProductId,BatchNumber,Price,Quantity,DateExpiry,OrderId)
VALUES(@ProductId,@BatchNumber,@Price,@Quantity,@DateExpiry,@OrderId)
";
            try
            {
                _dbConnection.Execute(sqlUpdateSupply, order, transaction);
                _dbConnection.Execute(sqlDeleteSupplyInfo, new { OrderId = order.Id }, transaction);

                foreach (var item in orderInfoList)
                {
                    _dbConnection.Execute(sqlUpdateSupplyInfo, item, transaction);
                }

                transaction.Commit();

                return true;
            }
            catch (Exception ex)
            {
                logger.Error("修改采购信息时出错", ex);
                transaction.Rollback();

                return false;
            }
        }

        public bool DeleteSupply(int id)
        {
            string sql = @"DELETE dbo.SupplyOrder WHERE Id = @OrderId;
DELETE dbo.SupplyInfo WHERE OrderId = @OrderId";

            return _dbConnection.Execute(sql, new { OrderId = id }) > 0;
        }

        public bool InStore(int orderId, int userId)
        {
            var result = _dbConnection.ExecuteScalar<int>("P_SupplyPutStorage", new { UserId = userId, OrderId = orderId }, commandType: CommandType.StoredProcedure);

            return result == 1;
        }

        public List<SupplyOrderDto> GetSupplyList(string where = "")
        {
            string sql = @"
SELECT * FROM (
	SELECT A.*, B.UserName AS CreateUserName, C.UserName AS PutUserName, D.CompanyName AS SupplierName
	FROM dbo.SupplyOrder AS A
	INNER JOIN dbo.SysUser AS B ON A.CreateUserId = B.Id
	LEFT JOIN dbo.SysUser AS C ON A.PutUserId = C.Id
	INNER JOIN dbo.SysSupplier AS D ON A.SupplierId = D.Id
)T ";
            if (!where.IsNullOrEmpty())
            {
                sql += where;
            }

            return _dbConnection.Query<SupplyOrderDto>(sql).AsList();
        }

        public SupplyOrderDto GetModel(int id)
        {
            string sql = @"
SELECT * FROM (
	SELECT A.*, B.UserName AS CreateUserName, C.UserName AS PutUserName, D.CompanyName AS SupplierName
	FROM dbo.SupplyOrder AS A
	INNER JOIN dbo.SysUser AS B ON A.CreateUserId = B.Id
	LEFT JOIN dbo.SysUser AS C ON A.PutUserId = C.Id
	INNER JOIN dbo.SysSupplier AS D ON A.SupplierId = D.Id
)T WHERE Id = " + id;

            return _dbConnection.QueryFirstOrDefault<SupplyOrderDto>(sql);
        }

        public List<SupplyInfoDto> GetSupplyInfoList(int orderId)
        {
            string sql = @"
SELECT B.ProductName,B.ProductCode,B.Specification,B.ManufacturingEnterprise,
B.MarketingUnit, A.*, CONVERT(DECIMAL(18,2),(A.Quantity * A.Price)) AS TotalMoney
FROM dbo.SupplyInfo AS A
INNER JOIN dbo.SysProduct AS B ON A.ProductId = B.Id
WHERE A.OrderId = @OrderId ";

            var list = _dbConnection.Query<SupplyInfoDto>(sql, new { OrderId = orderId });

            return list.AsList();
        }

        public List<SupplyReportDto> GetSupplyReportList(string where)
        {
            //            string sql = @"
            //SELECT * FROM (
            //    SELECT D.CreateUserId,D.SupplierId, B.ProductName,B.ProductCode,B.Specification,B.ManufacturingEnterprise,D.CreateTime,
            //    B.MarketingUnit, A.*,D.OrderCode,C.CompanyName,E.UserName, CONVERT(DECIMAL(18,2),(A.Quantity * A.Price)) AS TotalMoney
            //    FROM dbo.SupplyInfo AS A
            //    LEFT JOIN dbo.SysProduct AS B ON A.ProductId = B.Id
            //    INNER JOIN dbo.SupplyOrder AS D ON A.OrderId = D.Id AND D.State = 1
            //    LEFT JOIN dbo.SysSupplier AS C ON D.SupplierId = C.Id
            //    LEFT JOIN dbo.SysUser AS E ON E.Id = D.CreateUserId
            //) T  ";

            string sql = @"
SELECT * FROM (
    SELECT D.CreateUserId,D.SupplierId, B.ProductName,B.ProductCode,B.Specification,B.ManufacturingEnterprise,D.CreateTime,
    B.MarketingUnit, A.*,D.OrderCode,C.CompanyName,E.UserName, CONVERT(DECIMAL(18,2),(A.Quantity * A.Price)) AS TotalMoney
    FROM dbo.SupplyInfo AS A
    LEFT JOIN dbo.SysProduct AS B ON A.ProductId = B.Id
    INNER JOIN dbo.SupplyOrder AS D ON A.OrderId = D.Id AND D.State = 1
    LEFT JOIN dbo.SysSupplier AS C ON D.SupplierId = C.Id
    LEFT JOIN dbo.SysUser AS E ON E.Id = D.CreateUserId

	UNION ALL

	SELECT C.CreateUserId,D.SupplierId,B.ProductName,B.ProductCode,B.Specification,B.ManufacturingEnterprise,C.CreateTime,
	B.MarketingUnit, A.Id, A.ProductId, A.BatchNumber, A.Price, A.Quantity, A.DateExpiry, A.OrderId,
	C.OrderCode, E.CompanyName, F.UserName, CONVERT(DECIMAL(18,2),-(A.Quantity * A.Price)) AS TotalMoney 
	FROM dbo.SupplyReturnInfo AS A
	LEFT JOIN dbo.SysProduct AS B ON A.ProductId = B.Id
	INNER JOIN dbo.SupplyReturnOrder AS C ON A.OrderId = C.Id AND C.State = 1
	INNER JOIN dbo.SupplyOrder AS D ON C.SupplyOrderId = D.Id
	LEFT JOIN dbo.SysSupplier AS E ON D.SupplierId = E.Id
	LEFT JOIN dbo.SysUser AS F ON D.CreateUserId = F.Id
) T  ";

            if (!where.IsNullOrEmpty())
            {
                sql += where;
            }

            return _dbConnection.Query<SupplyReportDto>(sql).AsList();
        }

        public List<SupplyInfoDto> GetSupplyInfoReturnQuantityList(int orderId)
        {
            string sql = @"
    SELECT * FROM (
    SELECT B.ProductName,B.ProductCode,B.Specification,B.ManufacturingEnterprise,
    B.MarketingUnit, A.*, CONVERT(DECIMAL(18,2),(A.Quantity * A.Price)) AS TotalMoney,
		       A.Quantity - dbo.P_GetSupplyQuantityReturn(A.Id) AS QuantityReturned
    FROM dbo.SupplyInfo AS A
    INNER JOIN dbo.SysProduct AS B ON A.ProductId = B.Id
    WHERE A.OrderId = @OrderId 
)T ";
            return _dbConnection.Query<SupplyInfoDto>(sql, new { OrderId = orderId }).AsList();
        }
    }
}